<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>restsql文档</title>
    <style type="text/css">
        label {
            font-weight: bold;
        }

        span {
            color: red;
        }

        pre {
            font-size: 16px;
            color: green;
        }

        h3 {
            margin-top: 30px;
        }

        table {
            border-collapse: collapse;
            border: 1px solid #fff;
        }

        th, td {
            border: 1px solid #fff;
            padding: 5px;
        }

        #dictinary {
            position: fixed;
            top: 0px;
            right: 0px;
            bottom: 0px;
            width: 200px;
            background-color: #212528;
            padding: 10px;
            padding-top: 0px;
        }

            #dictinary a {
                display: block;
                color: #fff;
                text-decoration: none;
                line-height: 30px;
            }
    </style>
</head>
<body style="background-color:#000;color:#fff;padding:20px;font-size:16px;">
    <div id="dictinary" style="">
        <h3>目录</h3>
        <a href="#connection">数据库连接字符串</a>
        <a href="#url">url占位符说明</a>
        <a href="#var">服务端变量说明</a>
        <a href="#request">请求字段说明</a>
        <a href="#response">响应字段说明</a>
        <a href="#create">create</a>
        <a href="#delete">delete</a>
        <a href="#update">update</a>
        <a href="#read">read</a>
        <a href="#select">select</a>
        <a href="#page">page</a>
        <a href="#group">group</a>
        <a href="#transaction">transaction</a>

    </div>
    <h1>Restsql帮助文档</h1>
    <h3 id="connection">数据库连接字符串：</h3>
    <ul>
        <li><label>配置文件：</label><span>appsettings.json</span></li>
        <li>
            <label>配置项：</label>
            <pre>
"ConnectionStringConfigs": {
    "test":                                                             //数据库配置名称，未必是真实的数据库名
    {
      "ConnectionString": "server=.;database=test;uid=sa;pwd=123456;",  //ado.net标准数据库连接字符串，具体参考各数据库驱动程序集的相关说明
      "ConnectionClassName": "System.Data.SqlClient.SqlConnection"      //IDbConnection接口的实现类的类全名，具体参考各数据库驱动程序集的相关说明
    },
    ...
  }</pre>
        </li>
    </ul>

    <h3 id="url">url占位符说明：</h3>
    <table>
        <thead>
            <tr>
                <th>占位符名称</th>
                <th>说明</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>{数据库名}</td>
                <td>数据库配置名称，忽略大小写</td>
            </tr>
            <tr>
                <td>{表名}</td>
                <td>要操作的数据表名，大小写是否敏感取决于具体的数据库产品及其配置</td>
            </tr>
        </tbody>
    </table>

    <h3 id="var">服务端变量说明：</h3>
    <table>
        <thead>
            <tr>
                <th>变量名称</th>
                <th>变量说明</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>$GUID</td>
                <td>服务端会将其替换成一个guid值，常常在inser时使用该变量来生成主键</td>
            <tr>
                <td>$NOW</td>
                <td>服务端会将其替换成服务器的当前时间</td>
            </tr>
            <tr>
                <td>$TICKS</td>
                <td>服务端会将其替换成服务器的当前时间的长整型时间戳，注意该时间戳在高并发场景中并不具备唯一性</td>
            </tr>
        </tbody>
    </table>

    <h3 id="request">请求字段说明：</h3>
    <table>
        <thead>
            <tr>
                <th>字段名称</th>
                <th>数据类型</th>
                <th>字段说明</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>fields</td>
                <td>array&lt;string&gt;</td>
                <td>查询字段，如果为null，服务端默认为*</td>
            </tr>
            <tr>
                <td>data</td>
                <td>object</td>
                <td>要新增或修改的数据,key为字段名称，value为字段值</td>
            </tr>
            <tr>
                <td>orderBy</td>
                <td>string</td>
                <td>排序字段</td>
            </tr>
            <tr>
                <td>pageSize</td>
                <td>int</td>
                <td>分页大小</td>
            </tr>
            <tr>
                <td>pageIndex</td>
                <td>int</td>
                <td>第几页，从0开始</td>
            </tr>
            <tr>
                <td>queries</td>
                <td>array&lt;object&gt;</td>
                <td>查询表达式</td>
            </tr>
            <tr>
                <td>--field</td>
                <td>string</td>
                <td>查询字段</td>
            </tr>
            <tr>
                <td>--operator</td>
                <td>string</td>
                <td>只能是如下值之一：< , <= , == ,>= , <> , like，in，not in</td>
            </tr>
            <tr>
                <td>--value</td>
                <td>object</td>
                <td>查询值</td>
            </tr>
            <tr>
                <td>--prefix</td>
                <td>string</td>
                <td>连接词，可选值：and , or</td>
            </tr>
            <tr>
                <td>--leftBrackets</td>
                <td>string</td>
                <td>表达式左边的括号，可以是多个，如：(</td>
            </tr>
            <tr>
                <td>--rightBrackets</td>
                <td>string</td>
                <td>表达式左右边的括号，可以是多个，如：)</td>
            </tr>
            <tr>
                <td>groupFields</td>
                <td>array&lt;string&gt;</td>
                <td>分组字段，用于统计查询</td>
            </tr>
            <tr>
                <td>functions</td>
                <td>array&lt;object&gt;</td>
                <td>统计函数</td>
            </tr>
            <tr>
                <td>--functionName</td>
                <td>string</td>
                <td>函数名称</td>
            </tr>
            <tr>
                <td>--field</td>
                <td>string</td>
                <td>函数包裹的字段</td>
            </tr>
            <tr>
                <td>--resultName</td>
                <td>string</td>
                <td>给函数表达式取一个名字</td>
            </tr>
        </tbody>
    </table>

    <h3 id="response">响应字段说明：</h3>
    <table>
        <thead>
            <tr>
                <th>字段名称</th>
                <th>数据类型</th>
                <th>字段说明</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>success</td>
                <td>boolean</td>
                <td>操作结果</td>
            </tr>
            <tr>
                <td>message</td>
                <td>string</td>
                <td>错误信息</td>
            </tr>
            <tr>
                <td>rowCount</td>
                <td>int</td>
                <td>分页查询时，表示在未分页前，满足查询条件的数据总行数</td>
            </tr>
            <tr>
                <td>data</td>
                <td>object</td>
                <td>
                    当对数据库进行insert、update、delete操作时，其值为受影响的数据行数；<br />
                    当对数据库进行read操作时，其值为一个json键值对；<br />
                    当对数据库进行select、group、page时，其值为数组，数组的每个元素表示一行数据。<br />
                </td>
            </tr>
            <tr>
                <td>serverReceiveTime</td>
                <td>string</td>
                <td>服务器接收到请求的时间</td>
            </tr>
            <tr>
                <td>serverSendTime</td>
                <td>string</td>
                <td>服务器开始向客户端发送数据的时间</td>
            </tr>
        </tbody>
    </table>

    <h3 id="create">添加数据：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.create</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
{
    data:{
        "Field1":"$GUID",                       //服务端变量，服务端会用Guid.NewGuid().ToString()替换，常用于生成主键
        "Field2":123,                           //数值字段的值不加引号
        "Field3":true,                          //布尔字段的值不加引号
        "Field4":"字符串",                      //字符串字段值加引号
        "Field5":"2021-01-01 13:01:01",         //时间字段的值加引号，格式:yyyy-MM-dd HH:mm:ss
        "Field6":"$NOW",                        //服务端变量，服务端会用DataTime.Now替换
        ...
    }
}</pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,                            //操作结果
    "message": null,                            //错误消息
    "serverReceiveTime": "2021-04-21 08:39:57", //服务器接收到请求的时间
    "serverSendTime": "2021-04-21 08:39:57",    //服务器开始向客户端发送数据的时间
    "data": 1                                   //新增的数据行
}</pre>
        </li>
    </ul>

    <h3 id="delete">删除数据：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.delete</span>
        </li>
        <li>
            <label>method：</label><span>delete</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
{
    queries:[
        {
            field:"字段名",        
            value:"字段值"                       
        },{
            prefix:"and",                      //连接词
            field:"字段称",        
            operator:">",                      //操作符号,默认为=
            value:1                            //数值型值不加引号
        },{
            prefix:"and",          
            field:"字段称",        
            operator:"in",         
            value:[1,2,3]                      //in查询的value是一个数组
        }
        ...
    ]
}</pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,  
    "message": null,  
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57",
    "data": 1                                  //删除的行数
}</pre>
        </li>
    </ul>

    <h3 id="update">修改数据：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.update</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
{
    //更新的字段及其值
    data:{
        "Field1":"$GUID",                       //服务端变量，服务端会用Guid.NewGuid().ToString()替换，常用于生成主键
        "Field2":123,                           //数值字段的值不加引号
        "Field3":true,                          //布尔字段的值不加引号
        "Field4":"字符串",                      //字符串字段值加引号
        "Field5":"2021-01-01 13:01:01",         //时间字段的值加引号，格式:yyyy-MM-dd HH:mm:ss
        "Field6":"$NOW",                        //服务端变量，服务端会用DataTime.Now替换
        ...
    },
    //where条件
    queries:[
        {
            field:"字段名",        
            value:"字段值"                        
        },{
            prefix:"and",                       //连接词
            field:"字段称",        
            operator:">",                       //操作符号,默认为=
            value:1                             //数值型值不加引号
        },{
            prefix:"and",          
            field:"字段称",        
            operator:"in",         
            value:[1,2,3]                       //in查询的value是一个数组
        }
        ...
    ]
}</pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,
    "message": null,
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57", 
    "data": 1 
}</pre>
        </li>
    </ul>

    <h3 id="read">查询一条数据：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.read</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body</label>
            <pre>
{
    //要查询的字段
    fields:["字段1","字段2", ... ],
    //where条件
    queries:[
        {
            field:"字段名",        
            value:"字段值"                           
        },{
            prefix:"and",                          //连接词
            field:"字段称",        
            operator:">",                          //操作符号,默认为=
            value:1                                //数值型值不加引号
        },{
            prefix:"and",          
            field:"字段称",        
            operator:"in",         
            value:[1,2,3]                          //in查询的value是一个数组
        }
        ...
    ]
}
</pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,  
    "message": null,  
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57",
    "data": {
        Field1:"",
        Field2:"",
        ...
    } 
}</pre>
        </li>
    </ul>

    <h3 id="select">查询多条数据：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.select</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
{
    //要查询的字段
    fields:["字段1","字段2", ... ],
    //where条件
    queries:[
        {
            field:"字段名",        
            value:"字段值"            
        },{
            prefix:"and",          //连接词
            field:"字段称",        
            operator:">",          //操作符号,默认为=
            value:1                //数值型值不加引号
        },{
            prefix:"and",          
            field:"字段称",        
            operator:"in",         
            value:[1,2,3]          //in查询的value是一个数组
        },
        ...
    ]
}
            </pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,  
    "message": null,  
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57",
    "data": [
        {
            Field1:"",
            Field2:"",
            ...
        },
        ...
    ] 
}</pre>
        </li>
    </ul>

    <h3 id="page">分页查询：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.page</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
{
    orderBy:"排序字段",
    pageSize:"10",
    pageIndex:"0",
    //查询表达式
    queries:[
        {
            field:"字段名",        
            value:"字段值"            
        },{
            prefix:"and",          //连接词
            field:"字段称",        
            operator:">",          //操作符号,默认为=
            value:1                //数值型值不加引号
        },{
            prefix:"and",          
            field:"字段称",        
            operator:"in",         
            value:[1,2,3]          //in查询的value是一个数组
        },
        ...
    ]
}</pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,  
    "message": null,  
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57",
    "rowCount":10000,
    "data": [
        {
            Field1:"",
            Field2:"",
            ...
        },
        ...
    ] 
}</pre>
        </li>
    </ul>

    <h3 id="group">分组统计：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}/{表名}.group</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
{
    //分组字段
    groupFields:["分组字段名1","分组字段名2",...],
    //统计字段
    functions:[{
            functionName:"函数名称",
            field:"字段名称",
            resultName:"计算后的字段名"
        },
        ...
    ],
    //where条件
    queries:[
        {
            field:"字段名",        
            value:"字段值"          
        },{
            prefix:"and",          //连接词
            field:"字段称",        
            operator:">",          //操作符号,默认为=
            value:1                //数值型值不加引号
        },{
            prefix:"and",          
            field:"字段称",        
            operator:"in",         
            value:[1,2,3]          //in查询的value是一个数组
        },
        ...
    ]
}
          </pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,  
    "message": null,  
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57",
    "data": [
        {
            GroupField1:"",
            GroupField2:"",
            ...
            FunctionField1:"",
            FunctionField2:"",
            ...
        },
        ...
    ] 
}</pre>
        </li>
    </ul>

    <h3 id="transaction">事务提交：</h3>
    <ul>
        <li>
            <label>url：</label><span>/{数据库名}.transaction</span>
        </li>
        <li>
            <label>method：</label><span>post</span>
        </li>
        <li>
            <label>contentType：</label><span>application/json</span>
        </li>
        <li>
            <label>body：</label>
            <pre>
[
    {
        table:"表名",
        operator:"insert",                                  //可选值：insert,delete,update
        data:{
            "Field1":"$GUID",                               //服务端变量，服务端会用Guid.NewGuid().ToString()替换，常用于生成主键
            "Field2":123,                                   //数值字段的值不加引号
            "Field3":true,                                  //布尔字段的值不加引号
            "Field4":"字符串",                              //字符串字段值加引号
            "Field5":"2021-01-01 13:01:01",                 //时间字段的值加引号，格式:yyyy-MM-dd HH:mm:ss
            "Field6":"$NOW",                                //服务端变量，服务端会用DataTime.Now替换
            ...
        }
    },{
        table:"表名",
        operator:"delete",      
        queries:[
            {
                field:"字段名",        
                value:"字段值"          
            },{
                prefix:"and",          //连接词
                field:"字段称",        
                operator:">",          //操作符号,默认为=
                value:1                //数值型值不加引号
            },{
                prefix:"and",          
                field:"字段称",        
                operator:"in",         
                value:[1,2,3]          //in查询的value是一个数组
            },
            ...
        ]
    },{
        table:"表名",
        operator:"update",  
        //要修改的数据
        data:{
                "Field1":"$GUID",                               //服务端变量，服务端会用Guid.NewGuid().ToString()替换，常用于生成主键
                "Field2":123,                                   //数值字段的值不加引号
                "Field3":true,                                  //布尔字段的值不加引号
                "Field4":"字符串",                              //字符串字段值加引号
                "Field5":"2021-01-01 13:01:01",                 //时间字段的值加引号，格式:yyyy-MM-dd HH:mm:ss
                "Field6":"$NOW",                                //服务端变量，服务端会用DataTime.Now替换
                ...
        },   
        //where条件 
        queries:[
            {
                field:"字段名",        
                value:"字段值"           
            },{
                prefix:"and",          //连接词
                field:"字段称",        
                operator:">",          //操作符号,默认为=
                value:1                //数值型值不加引号
            },{
                prefix:"and",          
                field:"字段称",        
                operator:"in",         
                value:[1,2,3]          //in查询的value是一个数组
            },
            ...
        ]
    },
    ...
]
          </pre>
        </li>
        <li>
            <label>response：</label>
            <pre>
{
    "success": true,  
    "message": null,  
    "serverReceiveTime": "2021-04-21 08:39:57", 
    "serverSendTime": "2021-04-21 08:39:57",
    "data": true 
}</pre>
        </li>
    </ul>
</body>
</html>